package dao;

import pojo.car;
import pojo.user;
import util.C3P0Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Cardao {
    public List select(String carnumber) throws SQLException, ClassNotFoundException {
        List<car>carList=new ArrayList<>();
        Connection connection = C3P0Util.getConnection();
        String sql="select * from j_car where carnumber =?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, carnumber);
        ResultSet rs = statement.executeQuery();
        rs.next();
        int id = rs.getInt("id");
        String username = rs.getString("carnumber");
        String password = rs.getString("type");
        int state = rs.getInt("isnew");
        int repair=rs.getInt("isrepair");
        // System.out.println("id："+id+"，username："+username+"，password："+password+"，state："+state);
        // 将值存入 user 对象
        car car = new car(id, username, password, state,repair);
        // 将 user 对象放入集合中
        carList.add(car);
        C3P0Util.close(connection,statement,rs);
        return carList;
    }
    // 新增用户
    public int insert(car car) throws ClassNotFoundException, SQLException {
        Connection con = C3P0Util.getConnection();
        // 3，执行 sql 语句
        // 3，1 创建 sql 语句，问号表示为占位符，代表一个未知值
        String sql = "insert into J_car values(SEQ_J_USER.nextval,?,?,1,1) ";
        // 3，2 获取执行对象，执行语句 PreparedStatement
        // sql 语句的预编译，还没执行
        PreparedStatement ps = con.prepareStatement(sql);
        // 调用匹配类型方法，给占位符赋值
        ps.setString(1,car.getCarnumber());
        ps.setString(2,car.getType());
        // 执行 sql 语句
        int i = ps.executeUpdate();
        // 4，释放资源
        C3P0Util.close(ps,con);
        return i;
    }


    // 删除用户
    public int delete(car car) throws ClassNotFoundException, SQLException {
        Connection con = C3P0Util.getConnection();
        // 3，执行 sql 语句
        // 3，1 创建 sql 语句
        String sql = "delete from J_car where carnumber = ?";
        // 3，2 获取执行对象，执行语句 Statement
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,car.getCarnumber());
        // 执行 sql
        int i = ps.executeUpdate();
        // 4，释放资源
        C3P0Util.close(ps,con);
        return i;
    }

    // 修改用户
    public int update(car car) throws ClassNotFoundException, SQLException {
        if(null == car.getCarnumber()){
            car.setCarnumber("");
        }
        Connection con = C3P0Util.getConnection();
        // 3，执行 sql 语句
        // 3，1 创建 sql 语句
        String sql = "update J_Car set isrepair = ? where carnumber = ? ";
        // 3，2 获取执行对象，执行语句 Statement
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1,car.getIsrepair());
        ps.setString(2,car.getCarnumber());

        int i = ps.executeUpdate();
        // 4，释放资源
        C3P0Util.close(ps,con);

        return i;
    }


    // 查询用户
    public List<car> selectAll() throws ClassNotFoundException, SQLException {
        // 0，创建一个集合
        List<car> userList = new ArrayList<>();
        Connection con = C3P0Util.getConnection();
        // 3，创建 sql 语句执行对象
        // 3.1， 创建 sql 语句
        String sql = "select id,carnumber,type,isnew,isrepair from J_car";
        // 3.2， 创建 sql 执行对象 Statement ，执行 sql 语句
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        // 4，处理查询结果集
        while (rs.next()){
            int id = rs.getInt("id");
            String username = rs.getString("carnumber");
            String password = rs.getString("type");
            int state = rs.getInt("isnew");
            int repair=rs.getInt("isrepair");
            // System.out.println("id："+id+"，username："+username+"，password："+password+"，state："+state);
            // 将值存入 user 对象
            car car = new car(id, username, password, state,repair);
            // 将 user 对象放入集合中
            userList.add(car);
        }
        // 5，释放资源
        C3P0Util.close(rs,ps,con);
        // 返回集合给调用者
        return userList;
    }
}
